package com.neutech.dao.impl;

import com.neutech.dao.UserDAO;
import com.neutech.entity.User;
import com.neutech.util.JdbcUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class UserDAOImpl implements UserDAO {
    @Override
    public List<User> selectAll() {
        Connection connection = null;
        Statement statement = null;
        ResultSet resultSet = null;
        List<User> users = new ArrayList<>();
        String sql = "select id,user_name,user_password,user_phone," +
                "user_addr,user_createTime,user_updateTime,user_status from t_user";
        try{
            connection = JdbcUtil.getConnection();
            statement = JdbcUtil.getStatement(connection);
            resultSet = JdbcUtil.getResultSet(statement,sql);
            while (resultSet.next()){
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("user_name"));
                user.setPassword(resultSet.getString("user_password"));
                user.setPhone(resultSet.getString("user_phone"));
                user.setAddress(resultSet.getString("user_addr"));
                user.setUpdateTime(resultSet.getTimestamp("user_updateTime"));
                user.setStatus(resultSet.getInt("user_status"));
                users.add(user);
            }
        }catch (Exception e){
            e.printStackTrace();
            //当出现任何异常时，事务不提交
           JdbcUtil.rollback(connection);
        }finally {
            JdbcUtil.close(statement,connection,resultSet);
        }
        return users;

    }

    @Override
    public User selectById(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        User user = null;
//        String sql = "select id,user_name,user_password,user_phone,user_addr,user_createTime,user_updateTime,user_status from" +
//                "t_user where id = ?";
        String sql = "select id,user_name,user_password,user_phone," +
                "user_addr,user_createTime,user_updateTime,user_status from t_user where id = ?" ;
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setInt(1,id);
            resultSet = JdbcUtil.getResultSet(preparedStatement);
            if (resultSet.next()){
                user = new User();
                user.setId(resultSet.getInt("id"));
                user.setName(resultSet.getString("user_name"));
                user.setPassword(resultSet.getString("user_password"));
                user.setPhone(resultSet.getString("user_phone"));
                user.setAddress(resultSet.getString("user_addr"));
                user.setCreateTime(resultSet.getTimestamp("user_createTime"));
                user.setUpdateTime(resultSet.getTimestamp("user_updateTime"));
                user.setStatus(resultSet.getInt("user_status"));

            }
        }catch (Exception e){
            e.printStackTrace();
            //出现异常，事务不提交，回滚
            JdbcUtil.rollback(connection);
        }finally {
            JdbcUtil.close(resultSet,preparedStatement,connection);
        }
        return user;
    }

    @Override
    public void insert(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "insert into t_user values(null,?,md5(?),?,?,now(),now(),1)";
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,user.getName());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getPhone());
            preparedStatement.setString(4,user.getAddress());

            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
            JdbcUtil.rollback(connection);
        }finally {
            JdbcUtil.close(preparedStatement,connection);
        }
    }

    @Override
    public void delete(Integer id) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "delete from t_user where id = ?";
        try {
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setInt(1,id);
            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    @Override
    public void update(User user) {
        Connection connection = null;
        PreparedStatement preparedStatement = null;
        String sql = "update t_user set user_name = ?,user_password = md5(?),user_phone = ?,user_addr = ?,user_updateTime = now()" +
                "where id = ?";
        try{
            connection = JdbcUtil.getConnection();
            preparedStatement = JdbcUtil.getPreparedStatement(connection,sql);
            preparedStatement.setString(1,user.getName());
            preparedStatement.setString(2,user.getPassword());
            preparedStatement.setString(3,user.getPhone());
            preparedStatement.setString(4,user.getAddress());
            preparedStatement.setInt(5,user.getId());
            preparedStatement.executeUpdate();
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
